import pymysql


# 定义数据库工具类
class DBTools(object):
    @classmethod
    # 创建连接,类方法，直接使用类名调用
    def __create_conn(cls):
        conn = pymysql.connect(host="127.0.0.1",
                               port=3306,
                               user="root",
                               password="123456",
                               database="test_db",
                               charset="utf8")
        return conn
    # 查一条记录
    @classmethod
    def query_one(cls,sql):
        my_cursor = None
        my_conn = None
        res = None
        try:
            # 创建连接
            my_conn = DBTools.__create_conn()
            # 创建游标
            my_cursor = my_conn.cursor()
            # 执行查询语句
            my_cursor.execute(sql)
            res = my_cursor.fetchone()
        except Exception as err:
            print(f"查询异常：{str(err)}")
        finally:
            # 关闭游标
            my_cursor.close()
            # 关闭连接
            my_conn.close()
            return res
    # 增删改记录
    @classmethod
    def db_uid(cls,sql):
        my_conn = None
        my_cursor = None
        try:
            # 创建连接
            my_conn = DBTools.__create_conn()
            # 创建游标
            my_cursor = my_conn.cursor()
            # 执行增删改语句
            my_cursor.execute(sql)
            print(f"Affected rows:{my_conn.affected_rows()}")
            # 提交事务
            my_conn.commit()
        except Exception as err:
            # 回滚事务
            my_conn.rollback()
        finally:
            # 关闭游标
            my_cursor.close()
            # 关闭连接
            my_conn.close()


if __name__ == '__main__':
    result = DBTools.query_one("select * from t_book")
    print(result)
    DBTools.db_uid("UPDATE `t_book` SET `read_count` = 100 WHERE `title` = '笑傲江湖';")